Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / pcdbms.arc / MANUAL.2 < prev next >

Wrap

Text File | 1985-09-13 | 53.5 KB | 2,444 lines

CHAPTER 5 COMMAND REFERENCE OVERVIEW The commands summarized in Table 5-1 are implemented in PC-DBMS. Subsequent sections document them. COMMAND FUNCTION DEFINE invokes define utility to update schema DEKEY removes an index DISPLAY invokes display utility to update a table EXIT terminates execution of PC-DBMS KEY builds an index OPEN makes available an existing or new data base OUTPUT redirects output to file or printer REMOVE removes table from the data base SELECT retrieves records from one or two tables SHOW displays portion of schema TOLERANCE sets tolerance used for numeric comparisons Table 5-1. Summary of PC-DBMS commands. 5-1 COMMAND REFERENCE NOTATIONAL CONVENTIONS NOTATIONAL CONVENTIONS Syntax forms for the commands use the following notational conventions. - Lower case words indicate that you should substitute a word or value of your choice. - Upper case words are keywords and they should be used where shown. However, keywords can be truncated to three or more characters and can be entered in upper or lower case. - Brackets ([]) indicate optional elements. - Ellipses (...) indicate the preceding item(s) may be repeated one or more times. DEFINE The define command is issued after the open command is given for a new or existing data base. It invokes the define utility to enter definitions of tables and their fields. The syntax is: DEFINE The define utility is conversational. It presents two data entry screens. The first screen accepts the description of a table. The second screen accepts descriptions of that table's fields. The first or table screen prompts for the table's name, the specification of the file containing it, the record termination character and an optional verbal description. 5-2 COMMAND REFERENCE DEFINE When that screen is completed, the second screen type appears for entry of field data -- field name, data type, field width, indexing and an optional verbal description. When completed, the screen reappears blank for definition of the next field. If there is no "next" field, press F10 and the first data entry screen will reappear to define the next table. Then, if there is no "next" table, press F10 to exit the define utility. File specifications may not include path names but drive names may be used. If a drive name is given, the file must always be on that disk drive. If no drive name is given, the file must be on the logged (default) drive which you can set at the DOS level. The table screen prompts for a number to describe the end of record terminator. Enter 0 for no terminator, 1 for carriage return and 2 for carriage return-line feed. The choice of 2 facilitates processing the file outside PC-DBMS. Fields may have one of three data types: text, number or filler. Filler fields may contain anything -- blanks, numbers or text. The indexing and description information is not solicited for filler fields. Filler fields can be displayed with the select command but may not be used in where or sort clauses. The display utility ignores them; on input, they are blank filled. Filler fields are useful in masking columns of extraneous material in imported files or to insert blanks between fields during input. If a field is more than 141 characters wide, it cannot be indexed so the index prompt will not appear. If the data base is to include a table in an imported file, its definition must exactly describe the file. Filler fields of any desired width can be used. Any record whose first character is a backslash (\) will be considered deleted. 5-3 COMMAND REFERENCE DEKEY DEKEY The dekey command updates the schema so that it shows the named field as not keyed. It has no effect on the index file but the old index will never be usable. The syntax is: DEKEY qfn Qfn is a qualified field name. For example, DEKEY OPUS.WORK changes the schema so that field WORK in table OPUS is regarded as not keyed. When a field is indexed, it is necessary to maintain the indices when records are added, changed or deleted. Dekeying avoids this overhead. DISPLAY The display command projects a data entry form on the screen. The form consists of background text and field blocks corresponding to non-filler fields in the data table. The syntax is: DISPLAY [\INPUT] table_name [WHERE ] \QUERY \EDIT The display form operates on a single table given by table_name. That table may have up to 60 fields. Some tables may be rejected because all their non-filler fields cannot be squeezed onto the screen. A form will be created dynamically. Display fields corresponding to data fields appear as rectangular blocks; they are one or more characters wide and one or more lines high. 5-4 COMMAND REFERENCE DISPLAY The display command has four forms. With the \QUERY qualifier, a select command is composed for use as the next command. If \INPUT is specified, new records are entered and appended to the table. The default \EDIT qualifier permits retrieval, editing and deletion of existing records. The where clause form of the command also retrieves records for editing and deletion, those satisfying the where clause. Screen layouts are similar for all forms; only the bottom information lines differ. The various forms are discussed below. An example of the \INPUT form is DISPLAY\INPUT COMP In this example, a form will be projected with a blank field for each non-filler data field in the COMP table. Next to each field, its name will be shown. At the bottom right of the screen, INPUT will be displayed to identify the function. The rest of the bottom line gives other useful information. Fill in all fields (or leave some blank) and press F7 to store the new record. The form reappears with blank fields to enter another record. Press F10 to exit the display utility. However, exiting does not store any record still on screen. The \QUERY form of the display command helps compose certain select commands. The form shows QUERY at the bottom right and has blank fields. Fill in up to ten of them and press F7. This exits the display utility and restores the main screen. A select command resulting from the QUERY form entries will be in the command window. Edit, erase or execute it. It will have AND connectives, no parentheses, no negations, = comparisons and ALL in its display list. For example, suppose you invoke the display utility with DISPLAY\QUERY COMP 5-5 COMMAND REFERENCE DISPLAY You enter 1770 in the BORN field and Beethoven, L. in the NAME field. After pressing F7, the main screen reappears with the following in the command window: SELECT COMP.ALL WHERE NAME="Beethoven, L." AND BORN=1770 The command can be edited if it's unsuitable. For example, only certain fields might be desired in the output. The \EDIT form of the display command is used to find and edit or delete records. Two phases repeat in a cycle. First, a screen query is executed. Second, qualifying records are displayed one after the other on the form for editing and deletion. When the editing and deletion are complete, the blank query screen reappears to repeat the process. For example, to edit records in the COMP table, the command is DISPLAY\EDIT COMP The query phase of the cycle is almost identical to the DISPLAY\QUERY function. A form appears with QUERY at the bottom right and blank fields. Fill in up to ten fields and press F7 to execute the query. Alternatively, press F10 to exit from the display utility. If F7 was pressed, all the qualifying records are displayed one at a time on the form. The bottom line will now show editing prompts and EDIT at the right. As each qualifying record is displayed, do one of three things: leave it unchanged, edit it or delete it. To leave it unchanged, press F7. To edit it, make the desired changes on screen and press F6. To delete it, press F4. In any case, the next qualifying record will be displayed. However, to return to the first (query) phase, press F10. It is not possible to exit the display 5-6 COMMAND REFERENCE DISPLAY utility directly from the edit screen. After all qualifying records have been displayed, the screen will clear and the following message will appear: NO (MORE) RECORDS QUALIFY Press any key to continue After pressing a key, the query screen reappears to begin the query-edit cycle anew. Alternatively, press F10 to exit the display utility. DISPLAY\EDIT is the default. That is, if there is no where clause and no qualifier (\), then the \EDIT qualifier is assumed. The last form of the display command is the only one that uses the where clause. Any qualifier is ignored. This form of the command retrieves for editing and deletion records which satisfy the where clause. The where clause is documented under the select command. The default table name at the beginning of the clause is table_name. A second table can be referenced in the clause. Qualifying records are projected on the same edit screen used with the \EDIT form of the command. However, after the last record has been processed, the display utility is exited. For example, one way to retrieve Beethoven's record for editing is with this command: DISPLAY COMP WHERE NAME $ "Beethoven" When text fields are entered or displayed on screen forms, quote marks are treated as ordinary characters; they are not string delimiters. Hence, use one quote for each quote mark you wish to embed in the record. 5-7 COMMAND REFERENCE DISPLAY Numbers are entered according to the rules for numbers in commands. See COMMAND FORMATTING AND EXECUTION in chapter 4. The PC-DBMS editor operates in each field block. However, only replace mode is available. See THE PC-DBMS EDITOR in chapter 4. To move the cursor forwards from one field to the next, press tab. Press F9 to move it backwards. A help screen is available by pressing F1. Whenever records are added, deleted or modified by the display utility, the corresponding index file is automatically updated. However, if more than 13 fields are indexed, only the first 13 will be updated. The indices on the other fields will be incorrect. They should be dekeyed and then rebuilt with the key command. In practice, few tables have more than 13 keys. EXIT The exit command terminates execution of PC-DBMS and returns the user to the operating system. The command's syntax is EXIT but it can be issued by pressing F10. KEY The key command builds an index for the specified field and updates the schema to reflect that fact. The syntax is: KEY qfn 5-8 COMMAND REFERENCE KEY Qfn is a qualified field name. For example, KEY OPUS.WORK builds an index for the field WORK in the table OPUS. The index will be updated when the table is altered by the display command. Indexing will be aborted and an error message displayed if an attempt is made to key a field wider than 141 characters. OPEN Open is typically the first command the user gives. It makes an existing data base available. If the data base does not exist, its schema file will be created. The command syntax is: OPEN dbname Dbname is the name of the existing or new data base. For example, OPEN MUSIC. There is no close command. An open data base will be closed when the exit or open command is given. OUTPUT The output command is used to redirect the output of the show and select commands to a file or to a device such as a printer. The syntax is: OUTPUT [file-spec] File-spec is the name of the file or device. For example, OUTPUT FILE.DAT sends output to a disk file and OUTPUT LPT1: prints the output. If no file specification is given, the screen default will be restored. 5-9 COMMAND REFERENCE OUTPUT If file-spec is a disk file which already exists, it will be deleted and a new file of the same name created. The redirected output will include column headings. A file created by this command will not become part of the data base. That function is served by the "into" clause of the select command. REMOVE The remove command marks as deleted the schema definition of the named table(s). The syntax is: REMOVE table_name [...] For example, REMOVE CUSTOMERS BALANCES removes the two named tables. The data files are unaffected by this command but they can no longer be accessed. SELECT The select command retrieves data from the tables. It is capable of gathering data from one or two tables, filtering and sorting the retrieved records, displaying up to 12 of their fields in any order, and writing the result to a new table which becomes a part of the data base. The output ordinarily is written to the bottom half of the screen or whatever target is specified by the output command. However, output will be written to the designated new table if the into clause is used. The abbreviated syntax is: SELECT qfn's [WHERE ] [SORTED BY ] [INTO new_table_name] 5-10 COMMAND REFERENCE SELECT The first clause, qfn's, is the only necessary one. It is a list of qualified field names to be displayed. The fields can come from one or two tables. An example of a two table retrieval is SELECT COMP.NAME COMP.BORN OPUS.WORK. If more than twelve fields are specified, additional ones will be ignored and the query will be executed. The display will be truncated to accommodate screen width. To display all fields of a table, two special qfn's can be used. The table name and period are as usual but the field name is either ALL or ALLF. For example, SELECT COMP.ALL displays all non-filler fields of the COMP table. ALLF is used when the filler fields are also desired. Each time a table name is encountered (in a qfn), that table becomes the default. Subsequent field names which omit the table name and period reference the default table. For example, SELECT COMP.NAME COUNTRY OPUS.ALL displays two fields from COMP and all fields from OPUS. Figure 5-1 is an example of a simple select command. 5-11 COMMAND REFERENCE SELECT SELECT COMP.ALL NAME BORN DIED COUNTRY Tchaikovsky, P. 1840 1893 Russia Brahms, J. 1833 1897 Germany Verdi, G. 1813 1901 Italy Handel, G. 1685 1759 England Mozart, W. 1756 1791 Austria Bach, J. 1685 1750 Germany Chopin, F. 1810 1849 France Beethoven, L. 1770 1827 Germany Haydn, F. 1732 1809 Austria Mendelssohn, F. 1809 1847 Germany Debussy, C. 1862 1918 France Figure 5-1. A simple select command. The where clause is optional. A where clause contains one to ten comparisons (conditions) connected by ANDs and ORs. A record must satisfy the where clause to be displayed. For example, attaching the where clause WHERE BORN>1799 to the preceding query would eliminate from the display the five composers born before 1800. The eight comparisons are shown in Table 5-2. COMPARISON REPRESENTATION less than < less than or equal to <= equal to = greater than or equal to >= greater than > unequal to <> contains $ does not contain % Table 5-2. The eight comparison operators. 5-12 COMMAND REFERENCE SELECT On one side of the comparison there must be a field name, possibly qualified. On the other side, there may be another field name, possibly qualified, or a constant, numeric or text as appropriate. The elements on the two sides of the comparison must be of the same data type, both numeric or both text. Filler fields may not be used in comparisons. All eight operators can be used with text elements. Only the first six can be used with numeric elements. Spaces before and after the operator are optional. For numeric comparisons, the default tolerance is zero. The tolerance can be altered with the tolerance command. Text constants used with the first six comparison operators are right padded with blanks to match the length of the field element on the other side of the comparison. If a text constant is longer than the field element, the query will be rejected. Text comparisons use the ASCII collating sequence. Hence, WHERE NAME>"B" excludes NAMEs beginning with "A". The $ and % operators test whether the left hand element contains the right hand element. For example, NAME $ "Bee" is true for the Beethoven record but false for the other composers. The % operator returns the opposite result. The default table name in the where clause is inherited from the qfn list but is changed each time a qualified field name is encountered. Fields from different tables may appear in the same comparison. If qfn's from different tables are in the display list, it is likely there will be at least one cross-table comparison. For example, Figure 5-2 shows a join of composers (COMP) with their works (OPUS). 5-13 COMMAND REFERENCE SELECT SELECT COMP.ALL OPUS.WORK WHERE COMPOSER=COMP.NAME NAME BORN DIED COUNTRY WORK Brahms, J. 1833 1897 Germany Lullaby Beethoven, L. 1770 1827 Germany Symphony No. 5 Figure 5-2. Using select for a join. The 11 records in COMP are paired with the 2 in OPUS for 22 possible combinations. Of those 22, only the two which have identical values of COMP.NAME and OPUS.COMPOSER are displayed. (This conceptual execution of a join is not necessarily how PC-DBMS implements it.) PC-DBMS permits up to ten comparisons to be used, linked together by ANDs and ORs. For example, to find composers who lived entirely in the nineteenth century, use SELECT COMP.ALL WHE BORN>1799 AND DIED<1900 The complement to this set of composers is the set of composers not alive at any time during the nineteenth century. One way to find them is with the complement operator, NOT. For example, SELECT COMP.ALL WHE NOT ( BORN>1799 AND DIED<1900 ) NOT makes anything that is true false and vice versa. It is typically used on a combination of comparisons as above because the individual comparisons have their own complements. For example, NOT NAME>"Smith" is just NAME<= "Smith". Space between NOT and ( is optional. Parentheses, nested to any desired depth, can be used to alter the order of evaluation of a where clause. In the preceding comparison, parentheses assure that the two comparisons are evaluated and combined and then that result is negated. 5-14 COMMAND REFERENCE SELECT Like algebra, there is an order of evaluation in where clauses. Comparisons are evaluated (to true and false) first. Then NOTs are applied. AND comes next and OR is last. A table may be referenced for the first time in the command in the where clause. The sorted by clause is also optional. It contains a list of one to five field names from tables referenced earlier in the command. The first field name is the major sort key and the last is the minor sort key. Filler fields may not be used. The default sorting order is ascending (aardvarks before zebras). To get descending order on a key, follow it with "\d" or "\D". Use "\a" or "\A" to get ascending order, the default. For example, Figure 5-3 shows retrieval of composers in reverse order of birth and breaks ties (Bach and Handel) by alphabetizing their names. SEL COMP.ALL SOR BY BORN\D NAME NAME BORN DIED COUNTRY Debussy, C. 1862 1918 France Tchaikovsky, P. 1840 1893 Russia Brahms, J. 1833 1897 Germany Verdi, G. 1813 1901 Italy Chopin, F. 1810 1849 France Mendelssohn, F. 1809 1847 Germany Beethoven, L. 1770 1827 Germany Mozart, W. 1756 1791 Austria Haydn, F. 1732 1809 Austria Bach, J. 1685 1750 Germany Handel, G. 1685 1759 England Figure 5-3. A select command with sorting. 5-15 COMMAND REFERENCE SELECT The select command will abort with an error message if the output string is too wide. The output string contains the fields to be displayed plus two spaces between adjacent fields. Spaces are suppressed if the into clause is present. Add to the output string width the width of the widest sort field. The sum may not exceed 141. The optional into clause comes last. It makes the retrieved table part of the data base by writing the output to a file and making appropriate entries in the schema. Only an information message will be displayed on screen. Into is followed by the name for the new table. That name follows the conventions for naming tables but may not exceed eight characters. The name may not already be in use as a table name. The file for the table will have the table's name as its file name and DAT as its extension. It will be in the current directory of the logged drive. No drive will be recorded for it in the schema. The new table will have no indices. No more than two tables can be referenced prior to the into clause. An example of a complex select command follows. It illustrates simultaneous use of all the clauses. SELECT COMP.COUNTRY COMP.BORN OPUS.ALL WHERE COMPOSER=COMP.NAME AND NOT (DIED<1900 AND BORN>1799) SORT BY NAME BORN\D INTO NU_TABLE During output, scrolling can be suspended by pressing the control-num lock or control-S key and resumed by pressing any other key. The F10 key aborts output. 5-16 COMMAND REFERENCE SELECT Skeletal select commands can be composed easily with the DISPLAY\QUERY command. SHOW The show command displays a portion of the schema. The syntax is: SHOW [\FIELDS] [\DESCRIBE] [table_name ...] The optional qualifiers \FIELDS and \DESCRIBE determine what kind of information is retrieved from the schema. It is possible to use zero, one or two qualifiers. Note that SHOW\FIELDS\DESCRIBE has the same effect as SHOW\DESCRIBE\FIELDS. The FIELDS qualifier displays information on fields. Without this qualifier, information on tables is retrieved. The DESCRIBE qualifier retrieves verbal descriptions. Without this qualifier, tabular information is retrieved. If one or more tables is named, only information on those tables is retrieved. Otherwise, data on all tables is shown. Four examples follow. They show all possible combinations of the qualifiers (one, the other, both, neither). For brevity, schema information is retrieved for only one table. The unqualified show command produces tabular information on one or more tables. See Figure 5-4. 5-17 COMMAND REFERENCE SHOW SHOW COMP 'info on table COMP TABLE NAME FILE RECORDS FIELDS EOR MODIFIED COMP COMPOSER.DAT 11 6 2 01-11-85 Figure 5-4. The show command without qualifiers. The output exhibits the number of records and fields. EOR gives the end of record terminator; 0 means no terminator, 1 means carriage return and 2 means carriage return-line feed. With just the DESCRIBE qualifier, show displays a verbal description of one or more tables. See Figure 5-5. SHO\DESCRIBE COMP 'description of table COMP TABLE NAME DESCRIPTION COMP information about some composers Figure 5-5. The show\describe command. With just the FIELDS qualifier, show displays tabular information about the fields of one or more tables. See Figure 5-6. 5-18 COMMAND REFERENCE SHOW SHO\FIELDS COMP 'info on fields in table COMP FIELD NAME TYPE KEY FROM TO TABLE COMP NAME T N 1 19 BORN N N 20 23 filler1 F N 24 24 DIED N N 25 28 filler2 F N 29 29 COUNTRY T N 30 44 Figure 5-6. The show\fields command. The TYPE column indicates the field's data type: T for text, N for numeric and F for filler. KEY indicates if the field is indexed: Y for yes and N for no. The FROM and TO columns show each field's starting and ending positions in the data file. Show with both qualifiers (either order) exhibits verbal descriptions of the fields of one or more tables. See Figure 5-7. 5-19 COMMAND REFERENCE SHOW SHO\FIE\DES COMP 'descriptions of fields in COMP FIELD NAME DESCRIPTION TABLE COMP NAME the composer's last name and first initial BORN the year the composer was born filler1 DIED the year of the composer's death filler2 COUNTRY the composer's country of residence Figure 5-7. The show\fie\des command. During output, scrolling can be suspended by pressing the control-num lock or control-S key and resumed by pressing any other key. The F10 key aborts output. TOLERANCE The tolerance command sets the comparison tolerance used in processing where clauses. It affects only numerical comparisons. The syntax is TOLERANCE number The tolerance is set to the absolute value of number. The default value is zero, i.e., the value before this command is first given. Slight conversion errors occur in converting between decimal (used by people and stored in files) and binary (used internally by PC-DBMS) because the machine does not have infinite precision. Non-zero tolerance circumvents these errors. 5-20 COMMAND REFERENCE TOLERANCE The range created by tolerance can be exploited to simplify queries. For example, with zero tolerance, certain records are qualified by WHERE YEAR>=1895 AND YEAR<=1905 If tolerance is set to 5, the same records will, ignoring conversion errors, be qualified by WHERE YEAR=1900 Here are the formal tolerance rules. If F is a field with value a and r is another field or a user specified number and t is the tolerance, then the following are true conditions. F = r if and only if r-t <= a <= r+t F <> r if and only if a < r-t or a > r+t F > r if and only if a > r-t F >= r if and only if a >= r-t F < r if and only if a < r+t F <= r if and only if a <= r+t If NOT precedes a where clause condition, the operator of the condition will be complemented and the NOT removed when the condition is processed. For example, NOT YEAR > 1900 is processed as YEAR <= 1900. If tolerance is 2, then records with YEAR greater than 1898 should be excluded. In fact, records with YEAR less than or equal to 1902 will be included. 5-21 APPENDIX A KEYWORDS PC-DBMS keywords can be truncated to three or more characters and entered in upper, lower or mixed case. The keywords and their truncations should not be used for names of data bases, tables or fields. A list of keywords is in Table A-1. ALL INTO ALLF KEY AND OPEN BY OR DEFINE OUTPUT DEKEY QUERY DESCRIBE REMOVE DISPLAY SELECT EDIT SHOW EXIT SORTED FIELDS TOLERANCE INPUT WHERE Table A-1. The keywords of PC-DBMS. A-1 APPENDIX B THE RELATIONAL MODEL PC-DBMS is a data base management system which implements the relational model of data management. This appendix describes the relational model and how to design data bases around it. Relational data bases contain data tables. These are familiar objects; they have rows called records and columns called fields. Data bases typically have more than one table. For a given information content, a data base might have one, two, three or more tables. How many tables should contain that information? Which fields should be put into which tables? The purpose of this appendix is to help you answer those questions, i.e., to help you design your data base. If you're still looking for more information when you finish, you might consult C. J. Date, "An Introduction to Database Systems," (Reading, Massachusetts: Addison-Wesley Publishing Co., 1976). We will progress through several data bases, each better organized than the last. First, consider the one table data base in Figure B-1. B-1 THE RELATIONAL MODEL NAME_1 SSNO_1 NAME_2 SSNO_2 Jack 123-45-6789 Mary 987-65-4321 Figure B-1. A table with repeating fields. There are four fields and one record. It is obvious that the fields repeat; the second pair of fields contain the same sort of information as the first pair. This kind of table is called unnormalized. Suppose we have to find Mary's Social Security number. We would have to search the NAME_1 field and the NAME_2 field for "Mary". She might be in either one. This is a difficult query. More difficult still is a join, that is, connecting the records in this table with the records in another table on the basis of a common field like Social Security number. Each record has two such numbers. Which one should be used? Unnormalized tables can be normalized simply. Just make additional records of the repeating fields as in Figure B-2. NAME SSNO Jack 123-45-6789 Mary 987-65-4321 Figure B-2. A normalized table. Normalized tables are said to be in first normal form (1NF). Let's define a concept known as the primary key of a table. The primary key is a field or combination of fields which, when their values are specified, uniquely identifies no more than one record. For example, in a table with the two fields SSNO and BIRTH_PLACE, SSNO would be the primary key because for each listed Social B-2 THE RELATIONAL MODEL Security number there is only one birth place. BIRTH_PLACE cannot be the primary key because for a given BIRTH_PLACE there may be many Social Security numbers (people). If a table has a primary key, its records are unique. It may take several fields to comprise a primary key. For example, in a table which gives different people's incomes for different years, there are the fields SSNO, YEAR and INCOME. The first two uniquely identify the record; they are the primary key. Let's append a fourth field to this two field primary key table to study second normal form (2NF). The table is already in 1NF because its records do not contain lists or repeating fields. See Figure B-3. SSNO YEAR INCOME NAME 123-45-6789 1980 15000 Jack 987-65-4321 1980 22000 Mary 123-45-6789 1981 16500 Jack 987-65-4321 1981 24000 Mary Figure B-3. A table not in 2NF. Once both SSNO and YEAR are specified, the record can be identified and the other two fields looked up. Neither field in the primary key is sufficient to identify INCOME uniquely. For example, YEAR = 1980 identifies two records. The entire primary key, both fields, is necessary to identify INCOME. However, once SSNO is known, NAME can be looked up in Figure B-3. That is, NAME depends on less than all the fields in the primary key. In relational jargon, INCOME is fully functionally dependent on the primary key but NAME is only functionally dependent. B-3 THE RELATIONAL MODEL If all the non-key fields in a 1NF table are fully functionally dependent on the primary key, the table is in 2NF. Why should we care? Data base corruption is one reason we should care. For example, suppose in entering "Mary" twice, it was misspelled once as "Marie". Then, one Social Security number would correspond to two NAMEs. Note that full functional dependency implies only one value per non-key field (INCOME) for each primary key value. Incorrect replicates (Mary and Marie) cannot exist because multiple entries per primary key are precluded. Hence, consistency or integrity is one reason for 2NF. There are other reasons for preferring 2NF but let's go on to correct the problem. Split one table into two. See Figure B-4. SSNO YEAR INCOME SSNO NAME 123-45-6789 1980 15000 123-45-6789 Jack 987-65-4321 1980 22000 987-65-4321 Mary 123-45-6789 1981 16500 987-65-4321 1981 24000 Figure B-4. Tables in 2NF. Both tables are in 2NF. In the left table, SSNO and YEAR are the primary key and INCOME is fully functionally dependent on them. In the right table SSNO is the primary key and NAME is the non-key field (but full functional dependency is immaterial because of the one field primary key). Note that in the two table version of the data base, we can record Fred's Social Security number (right table) although there isn't enough information to make an entry for him in the left table. This capability did not exist in the single table (1NF) version. B-4 THE RELATIONAL MODEL The table in Figure B-5 is in 2NF but it still has problems. SSNO AGE HOME_TOWN AVG_TEMP 123-45-6789 32 Boston 53 987-65-4321 19 Dallas 59 369-47-5814 50 El Paso 59 421-63-9631 41 Boston 47 Figure B-5. A table not in 3NF. SSNO is the primary key of this table. The non-key fields, AGE, HOME_TOWN and AVG_TEMP, are functionally dependent on the primary key. (They are fully functionally dependent, too, but this is trivial for a one field primary key.) Observe that Boston has two average temperatures and they are different. True, this illustrates a data entry error. However, it is possible to structure a data base to guard against such errors. That's what third normal form (3NF) is for. In Figure B-5, there is a functional dependency between non-key fields. That is, AVG_TEMP is dependent on HOME_TOWN. Once HOME_TOWN is known, an uncorrupted data base would tell us AVG_TEMP. Dependency among non-key fields is permissible in 2NF but not in 3NF. The remedy, as before, is to split one table into two. See Figure B-6. SSNO AGE HOME_TOWN HOME_TOWN AVG_TEMP 123-45-6789 32 Boston Boston 53 987-65-4321 19 Dallas Dallas 59 369-47-5814 50 El Paso El Paso 59 421-63-9631 41 Boston Figure B-6. Tables in 3NF. B-5 THE RELATIONAL MODEL In each new table of Figure B-6, the left field is the primary key. In the left table, the two non-key fields are mutually independent -- knowing either does not identify the other. Hence, the left table is in 3NF. There is only one non-key field in the right table so 2NF implies 3NF. Aside from avoiding the corruption encountered earlier, it is also possible to record the AVG_TEMP for a HOME_TOWN even if we don't know the SSNO of anyone who lives there. That is, we can have a record in the right table without a correspondent in the left table. Similarly, we can have a record in the left table even if we don't know the AVG_TEMP of that person's HOME_TOWN. To sum up, a table in 1NF has no lists or repeating fields in its records. A 2NF table is a 1NF table in which each non-key field is fully functionally dependent on the entire primary key. Finally, a 3NF table is a 2NF table in which the non-key fields are mutually independent. A data base consisting of third normal form tables provides the most protection against corruption. It also permits tables to be modified individually, e.g., we don't have to know the age and Social Security of someone in Detroit to enter that city's average temperature in Figure B-6. B-6 APPENDIX C USER SUPPORTED SOFTWARE PC-DBMS is distributed as user supported software. You are encouraged to make copies of the distribution diskette for other potential users. There is no license agreement but please pass along all files without modification. User groups and similar organizations may impose a nominal charge to cover the cost of duplication. The user supported concept of software is a significant innovation (not mine) which benefits both user and author. For the user, there is the convenience of leisurely evaluation of the program on your own system in your own home or office to see if it's right for your application. The low cost of user supported software distribution also makes possible a voluntary payment lower than the price of commercial software. And, of course, there's no copy protection to diminish the utility of the program. For the author, there is the satisfaction of getting a far larger number of copies into use than is possible with conventional marketing. Also, the interference of piracy is turned into a reinforcement in the distribution of the program. Finally, if you have dealt with a publisher, print or electronic, you understand the undesirability of that alternative. C-1 USER SUPPORTED SOFTWARE However, it doesn't work unless you want it to. If you find PC-DBMS useful, I hope you will see fit to make a voluntary payment of $60, by check or through your bank credit card. Beyond what you already have, this is what you will receive from me. - A typeset, bound edition of the User's Guide. - A mailed copy of the next version of PC-DBMS without charge. - Support. Comments, suggestions and especially bug reports are welcome from anyone. However, only users who have made a voluntary payment can expect replies to their questions. Support will be provided on a best efforts basis by mail and through Compuserve. My Compuserve id is 74216,3033. - My thanks. PC-DBMS took a year to develop, test and document. I enjoyed every minute of it. Now, I ask that you send your voluntary payment with the registration form. A copy of that form is on the next page or, if you wish to leave your manual intact, another copy is in the file REQUEST.DOC. If you need an invoice, you'll find one following the registration form in both the manual and in REQUEST.DOC. For quantity purchases of PC-DBMS, contact Kware. Thank you. C-2 PC-DBMS REGISTRATION FORM Name _____________________________ Phone ____________ Company _____________________________ Address _____________________________ _____________________________ City _____________________________ State ______________ Zip __________ version number ___________ source of your copy (club or bulletin board), if known _____________________________________________ To use a credit card, please indicate: Visa __ or Mastercard __ card number ____________________________ expiration month/year ______/________ signature ______________________________ To pay by check, please make payable to Kware for $60. Virginia residents add 4% sales tax. Kware Thank you. P. O. Box 16206 Arlington, Va. 22215 Paul Kobrin C-3 INVOICE Vendor: Kware, Inc. Federal EIN: 54-1304150 P. O. Box 16206 Arlington, Va. 22215 Date: / / Description Qty Price Extension ----------- --- ----- --------- PC-DBMS, 1 $60.00 $60.00 User's Guide, support --------- Sub-total 60.00 Tax @ 4% for Va. residents ($2.40) --------- Amount due You may retain this invoice. Please make check payable to Kware. C-4 INDEX All keyword . . . . . . . . . 3-5, 5-5, 5-11 Allf keyword . . . . . . . . . 5-11 Alphabetize . . . . . . . . 3-6, 5-15 And keyword . . . . . . . . . 3-6, 5-5, 5-12, 5-14 to 5-15 Apostrophe comment delimiter . . . . 4-5 Ascii . . . . . . . . . . . 4-2, 5-13 Backslash in deleted records . . . . 4-2, 5-3 Backspace key . . . . . . . 3-4, 3-6, 3-13, 4-4 Blank padding . . . . . . . 5-13 Brackets . . . . . . . . . . 5-2 Carriage return . . . . . . 3-4, 4-4 Case sensitivity . . . . . . . 4-3, 5-2, A-1 Character string . . . . . . 4-5 see also text string Close command . . . . . . . 5-9 Color . . . . . . . . . . . 2-1, 3-2 Command window . . . . . . . 3-4, 4-3, 4-5, 5-6 Comments in commands . . . . . . . 4-5 Comparison . . . . . . . . . 5-12 to 5-14, 5-20 see also condition Compuserve . . . . . . . . . C-2 Condition . . . . . . . . . 3-5, 5-12, 5-21 see also comparison Continuation character . . . 4-4 Control-num lock . . . . . . 5-16, 5-20 Control-s . . . . . . . . . 3-7, 3-9, 5-16, 5-20 Cursor keys . . . . . . . . 3-4, 3-11, 3-13, 4-4 Data dictionary . . . . . . 3-10 see also schema Default table . . . . . . . 3-7 to 3-8, 5-7, 5-11, 5-13 Index-1 Define command . . . . . . . 3-12, 5-2 Define utility . . . . . . . 3-12, 3-15, 4-2, 5-2 Dekey command . . . . . . . 3-15 to 3-16, 5-4 Delete key . . . . . . . . . 3-6, 4-4 Directory . . . . . . . . . 4-1, 5-16 see also subdirectory Display command . . . . . . 3-10, 5-4, 5-7, 5-9 Display utility . . . . . . 3-10 to 3-11, 3-15, 4-3, 5-3, 5-5 to 5-8 Distribution diskette . . . 2-1 Dos . . . . . . . . . . . . 2-1, 2-3, 3-2, 3-16, 5-3 Drive names . . . . . . . . 5-3 Editor . . . . . . . . . . . 4-3, 5-8 Ellipses . . . . . . . . . . 5-2 End key . . . . . . . . . . 4-4 Errors see messages Exit command . . . . . . . . 3-16, 4-5, 5-8 Export data . . . . . . . . . . . 4-2 F1 key . . . . . . . . . . . 3-3, 3-11, 4-5, 5-8 F10 key . . . . . . . . . . 3-7, 3-11 to 3-12, 3-15 to 3-16, 4-5, 5-3, 5-5 to 5-7, 5-16, 5-20 F2 key . . . . . . . . . . . 4-6 F3 key . . . . . . . . . . . 3-4, 4-5 F4 key . . . . . . . . . . . 3-12, 5-6 F6 key . . . . . . . . . . . 3-12, 5-6 F7 key . . . . . . . . . . . 3-4, 3-11 to 3-12, 4-5, 5-5 to 5-6 F9 key . . . . . . . . . . . 3-11, 4-5, 5-8 Field block . . . . . . . . 3-10 to 3-11, 4-3, 5-4, 5-8 Fields filler . . . . . . . . . . 3-9, 5-3, 5-13, 5-15 functionally dependent . . B-3 to B-6 numeric . . . . . . . . . 3-9, 5-3 text . . . . . . . . . . . 3-9, 5-3 File specifications . . . . 5-3 Files character positions in . . 3-9 data . . . . . . . . . . . 4-1 to 4-2, 5-2, 5-16, 5-19 def . . . . . . . . . . . 4-1 distribution . . . . . . . 2-1, C-1 Index-2 idx . . . . . . . . . . . 4-1 index . . . . . . . . . . 4-1, 5-4, 5-8, 5-10 maintenance . . . . . . . 3-15 schema . . . . . . . . . . 4-1 to 4-2, 5-9 Flying reform editor . . . . . . . . . . 4-3 to 4-4 Functionally dependent see fields Help . . . . . . . . . . . . 3-3, 3-11, 4-5 to 4-6, 5-8 Help menu . . . . . . . . . 3-3 Home key . . . . . . . . . . 4-4 Import data . . . . . . . . . . . 4-2, 5-3 Index . . . . . . . . . . . 3-15 to 3-16, 5-4, 5-8 to 5-9, 5-16 see also key Insert mode editor . . . . . . . . . . 4-3 Installation . . . . . . . . 2-2 Into clause . . . . . . . . 3-7 to 3-8, 3-15, 5-10, 5-16 Invoice . . . . . . . . . . C-4 Join relational operation . . . 3-7 to 3-8, 3-16, 5-13 to 5-14, B-2 Key . . . . . . . . . . . . 3-15 to 3-16, 5-19 see also index Key command . . . . . . . . 3-15, 5-8 Keyword . . . . . . . . . . 4-3, A-1 see also truncation Main screen . . . . . . . . 3-2, 3-11 to 3-13, 3-15, 4-3, 4-5, 5-5 to 5-6 Menu see help menu Message window . . . . . . . 3-4 Messages error or warning . . . . . 3-4, 4-5 Names data base . . . . . . . . 4-3, A-1 field . . . . . . . . . . 4-3, A-1 Index-3 qualified field . . . . . 3-7, 4-3, 5-4, 5-9, 5-11, 5-13 table . . . . . . . . . . 4-3, A-1 into clause . . . . . . 5-16 Normal form . . . . . . . . B-2 to B-3, B-5 to B-6 Not keyword . . . . . . . . . 5-14 to 5-15, 5-21 Numbers representation . . . . . . 4-4 Open command . . . . . . . . 3-4, 5-9 Or keyword . . . . . . . . . 3-6, 5-12, 5-14 to 5-15 Output command . . . . . . . 5-9 to 5-10 Output window . . . . . . . 3-2 Parentheses . . . . . . . . 5-14 Path names . . . . . . . . . 5-3 Primary key . . . . . . . . B-2 to B-6 Printer . . . . . . . . . . 5-9 Qualified field names see names Quote mark text delimiter . . . . . . 3-6, 4-5, 5-7 Recall command . . . . . . . . . 3-8 Record terminator . . . . . 5-3, 5-18 Relational model . . . . . . B-1 Remove command . . . . . . . 3-10, 4-2, 5-10 Replace mode editor . . . . . . . . . . 4-3 to 4-4, 5-8 Schema . . . . . . . . . . . 3-10, 3-12, 4-1 to 4-2, 5-4, 5-8 to 5-10, 5-16 to 5-17 Scroll . . . . . . . . . . . 3-6 to 3-7, 3-9, 5-20 Select command . . . . . . . 3-5, 3-7 to 3-8, 4-3, 5-3, 5-5, 5-9 to 5-11 Show command . . . . . . . . 3-8, 3-10, 5-9, 5-17 Sort clause . . . . . . . . 3-6, 5-3, 5-15 maximum width . . . . . . 5-16 Space . . . . . . . . . . . 4-4, 5-13 to 5-14 Subdirectory . . . . . . . . 2-3, 3-2 see also directory Index-4 Support . . . . . . . . . . C-2 Tab key . . . . . . . . . . 3-11, 5-8 Text string . . . . . . . . 3-6 see also character string Tolerance command . . . . . 5-13, 5-20 Truncation keyword . . . . . . . . . 3-8, 4-5, 5-2, A-1 User supported software . . C-1 Warnings see messages Welcome screen . . . . . . . 3-2 Where clause . . . . . . . . 3-5 to 3-6, 3-16, 5-3, 5-5, 5-7, 5-12 to 5-13 order of evaluation . . . 5-15 Word wrapping editor . . . . . . . . . . 3-4, 4-3 Index-5